
PRODUCT_BASE_PRICE_SQL = '''
select
--product_category_id,product_id,product_base_price,update_date
product_id,product_base_price
from 
(
    SELECT
    product_category_id,product_id,product_base_price,update_date,
    Row_Number() OVER (partition by concat(cast(product_category_id as varchar), cast(product_id as varchar)) ORDER BY update_date desc) product_rank
    FROM
    dw.dw_manual_recycle_product_base_price
)a where product_rank=1
'''

CATEGORY_PROPERTY_VALUE_PRICE_SQL = '''
select
product_category_id,property_name_id,property_value_back_id,property_value_back_price,update_date
from 
(
    SELECT
    product_category_id,property_name_id,property_value_back_id,property_value_back_price,update_date,
    Row_Number() OVER (partition by concat(cast(product_category_id as varchar),cast(property_value_back_id as varchar)) ORDER BY update_date desc) pv_rank
    FROM
    dw.dw_manual_recycle_category_property_value_price
)a where pv_rank=1
'''

TEMPLATE_LEVEL_RATE_SQL = '''
select
product_level_template_id,product_level_id,product_level_rate
from 
(
    SELECT
    product_category_id,product_level_template_id,product_level_id,product_level_rate,update_date,
    Row_Number() OVER (partition by concat(cast(product_category_id as varchar), cast(product_level_template_id as varchar),cast(product_level_id as varchar)) ORDER BY update_date desc) level_rank
    FROM
    dw.dw_manual_recycle_template_level_rate
)a where level_rank=1
'''


SKU_PROPERTY_SQL = '''
select
distinct product_category_id,product_category_name,product_id,product_name,product_sku_id,product_sku_name,property_name_id,
property_name_name,product_property_value_id,product_property_value_name,product_brand_id,product_brand_name
from
rpt.rpt_centre_sku_property_analysis
where product_id in ({})
'''


CATEGORY_PRODUCT_PRICE_SQL = '''
select
distinct product_id,0 as product_base_price
from
rpt.rpt_centre_sku_property_analysis
where product_category_id = 354 and product_id is not null
'''




# SKU_PROPERTY_DIY_PC_SQL = '''
# select
# distinct product_category_id,product_category_name,product_id,product_name,product_sku_id,product_sku_name,property_name_id,
# property_name_name,product_property_value_id,product_property_value_name
# from
# rpt.rpt_centre_sku_property_analysis
# where product_category_id=354
# '''


# PRODUCT_LEVEL_SQL = '''
# select
# distinct product_id,product_level_template_id
# from
# dim.dim_product
# where
# product_id in ({})
# '''

PRODUCT_LEVEL_NAME_SQL = '''
select
distinct product_level_id,product_level_name
from
dim.dim_product_level
'''


PRODUCT_LEVEL_SQL = '''
select 
distinct product_id,secondary_level_template_id as product_level_template_id
from ods.ods_opt_foundation_secondary_product_template_mapping 
where product_id in ({})
'''
